﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using Ivy.Common.Framework.Base;

namespace Ivy.Common.Framework.DataAccess
{
    public static class DatabaseCommon
    {
        #region Common SQL Command Text
        public static string InsertSQlCommandTextForFieldsDataEntityBase()
        {
            string lInsertCommandText = string.Empty;
            StringBuilder CommandTextBuilder = new StringBuilder();
            CommandTextBuilder.Append("[Sys_DateOfCreation], ");
            CommandTextBuilder.Append("[Sys_DateOfModification], ");
            CommandTextBuilder.Append("[Sys_CreatedByID], ");
            CommandTextBuilder.Append("[Sys_ModifiedByID], ");
            CommandTextBuilder.Append("[Sys_DeleteType], ");
            CommandTextBuilder.Append("[Sys_IsDeleted]), ");
            CommandTextBuilder.Append("[Sys_OrganizationID], ");
            CommandTextBuilder.Append("[Sys_InitialPrimaryKeys], ");
            CommandTextBuilder.Append("[Sys_WorkstationName] ");

            lInsertCommandText = CommandTextBuilder.ToString();
            
            return lInsertCommandText;
        }
        public static string InsertSQlCommandTextForParameterDataEntityBase()
        {
            string lInsertCommandText = string.Empty;
            StringBuilder CommandTextBuilder = new StringBuilder();
            CommandTextBuilder.Append("@Sys_DateOfCreation, ");
            CommandTextBuilder.Append("@Sys_DateOfCreation, ");
            CommandTextBuilder.Append("@Sys_CreatedByID, ");
            CommandTextBuilder.Append("@Sys_ModifiedByID, ");
            CommandTextBuilder.Append("@Sys_DeleteType, ");
            CommandTextBuilder.Append("0, ");
            CommandTextBuilder.Append("@Sys_OrganizationID, ");
            CommandTextBuilder.Append("@Sys_InitialPrimaryKeys, ");
            CommandTextBuilder.Append("@Sys_WorkstationName ");
            lInsertCommandText = CommandTextBuilder.ToString();

            return lInsertCommandText;
        }
        #endregion Common SQL Command Text

        #region Common Translation Pattern for SQL Parameters
        /// <summary>
        /// InsertStoredProcForParameterDatEntityBase():- This metod is used to assign entity instance attribute values to the ADO Command Parameter objects.
        /// This is helpful for Insert StoredProc execution.
        /// </summary>
        /// <param name="pDbCommand"></param>
        /// <param name="pDataEntity"></param>
        /// <returns></returns>
        public static DbCommand InsertStoredProcForParameterDatEntityBase(DbCommand pDbCommand, DataEntityBase pDataEntity)
        {
            if (pDataEntity.Sys_CreatedByID == string.Empty)
            {
                throw new SystemException("DAO Error:- New [" + pDataEntity.GetType().ToString() + "] Entity is being inserted without [Sys_CreatedByID]");
            }
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_ModifiedByID", ParameterDirection.Input, DbType.String, pDataEntity.Sys_ModifiedByID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_CreatedByID", ParameterDirection.Input, DbType.String, pDataEntity.Sys_CreatedByID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_DeleteType", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(pDataEntity.Sys_DeleteType));
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, pDataEntity.Sys_OrganizationID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_InitialPrimaryKeys", ParameterDirection.Input, DbType.String, pDataEntity.Sys_InitialPrimaryKeys);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_WorkstationName", ParameterDirection.Input, DbType.String, pDataEntity.Sys_WorkstationName);
            return pDbCommand;
        }
        
        /// <summary>
        /// UpdateStoredProcForParameterDatEntityBase():- This metod is used to assign entity instance attribute values to the ADO Command Parameter objects.
        /// This is helpful for Update StoredProc execution.
        /// </summary>
        /// <param name="pDbCommand"></param>
        /// <param name="pDataEntity"></param>
        /// <returns></returns>
        public static DbCommand UpdateStoredProcForParameterDatEntityBase(DbCommand pDbCommand, DataEntityBase pDataEntity)
        {
            if (pDataEntity.Sys_ModifiedByID == string.Empty)
            {
                throw new SystemException("DAO Error:- Update [" + pDataEntity.GetType().ToString() + "] Entity is being inserted without [Sys_ModifiedByID]");
            }
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_ModifiedByID", ParameterDirection.Input, DbType.String, pDataEntity.Sys_ModifiedByID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_DeleteType", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(pDataEntity.Sys_DeleteType));
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_IsDeleted", ParameterDirection.Input, DbType.Boolean, Convert.ToBoolean(pDataEntity.Sys_IsDeleted));
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, pDataEntity.Sys_OrganizationID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_InitialPrimaryKeys", ParameterDirection.Input, DbType.String, pDataEntity.Sys_InitialPrimaryKeys);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_WorkstationName", ParameterDirection.Input, DbType.String, pDataEntity.Sys_WorkstationName);
            return pDbCommand;
        }
        public static DbCommand DeleteStoredProcdureForParameterDataEntity(DbCommand pDbCommand, DataEntityBase pDataEntity)
        {
            if (pDataEntity.Sys_ModifiedByID == string.Empty)
            {
                throw new SystemException("DAO Error:- Update [" + pDataEntity.GetType().ToString() + "] Entity is being deleted without [Sys_ModifiedByID]");
            }
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_ModifiedByID", ParameterDirection.Input, DbType.String, pDataEntity.Sys_ModifiedByID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_DeleteType", ParameterDirection.Input, DbType.Int32, pDataEntity.Sys_DeleteType);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, pDataEntity.Sys_OrganizationID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_InitialPrimaryKeys", ParameterDirection.Input, DbType.String, pDataEntity.Sys_InitialPrimaryKeys);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_WorkstationName", ParameterDirection.Input, DbType.String, pDataEntity.Sys_WorkstationName);
            return pDbCommand;
        }
        public static DbCommand DefaultStoredProcParameterValuesForGetByCriteria(DbCommand pDbCommand, EntityBase pEntityBase)
        {
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_DateOfCreation", ParameterDirection.Input, DbType.DateTime, pEntityBase.Sys_DateOfCreation);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_DateOfModification", ParameterDirection.Input, DbType.DateTime, pEntityBase.Sys_DateOfModification);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_ModifiedByID", ParameterDirection.Input, DbType.String, pEntityBase.Sys_ModifiedByID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_CreatedByID", ParameterDirection.Input, DbType.String, pEntityBase.Sys_CreatedByID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_DeleteType", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(pEntityBase.Sys_DeleteType));
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, pEntityBase.Sys_OrganizationID);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_InitialPrimaryKeys", ParameterDirection.Input, DbType.String, pEntityBase.Sys_InitialPrimaryKeys);
            DataAccessProviderUtility.AddParameter(pDbCommand, "@Sys_WorkstationName", ParameterDirection.Input, DbType.String, pEntityBase.Sys_WorkstationName);
            return pDbCommand;
        }
        #endregion Common Translation Pattern for SQL Parameters

        #region Common Translation Pattern for Database Reader Fields to Entity Objects
        /// <summary>
        /// TranslateSystemFieldsFromReaderToEntity():- This method is used for mapping database fielsds present in a DataReader to entity instance attributes. 
        /// This is helpful when retriving data through data reader to assing reader field values to entity attributes.
        /// </summary>
        /// <param name="pDataEntityBase"></param>
        /// <param name="pDbDataReader"></param>
        public static void TranslateSystemFieldsFromReaderToEntity(DataEntityBase pDataEntityBase, DbDataReader pDbDataReader)
        {
            pDataEntityBase.Sys_DateOfCreation = Convert.ToDateTime(pDbDataReader["Sys_DateOfCreation"]);
            pDataEntityBase.Sys_DateOfModification = Convert.ToDateTime(pDbDataReader["Sys_DateOfModification"]);
            pDataEntityBase.Sys_CreatedByID = Convert.ToString(pDbDataReader["Sys_CreatedByID"]);
            pDataEntityBase.Sys_ModifiedByID = Convert.ToString(pDbDataReader["Sys_ModifiedByID"]);
            pDataEntityBase.Sys_DeleteType = (DeleteType)pDbDataReader["Sys_DeleteType"];
            pDataEntityBase.Sys_IsDeleted = Convert.ToBoolean(pDbDataReader["Sys_IsDeleted"]);
            pDataEntityBase.Sys_OrganizationID = Convert.ToInt32(pDbDataReader["Sys_OrganizationID"]);
            pDataEntityBase.Sys_WorkstationName = Convert.ToString(pDbDataReader["Sys_WorkstationName"]);

            pDataEntityBase.ConcurrencyTimeStamp = Convert.ToDateTime(pDbDataReader["Sys_DateOfModification"]);
            pDataEntityBase.EntityState = EntityState.UnChanged;
        }
        #endregion Common Translation Pattern for System Fields
    }
}
